create or replace function get_class_by_human_id(
        p_humanId integer)
    returns text as
$$
begin
    return row_to_json(classes) from humans
        inner join classes
            on humans.classId=classes.id
        where humans.id = p_humanId;
end;
$$
language plpgsql;

create or replace function upsert_class(
        p_year integer,
        p_institution varchar(128),
        p_photoLocation varchar(256),
        p_photoWidth integer,
        p_photoHeight integer)
    returns text as
$$
begin
    loop
        -- first try to update the key
        update classes set
                year=p_year,
                institution=p_institution,
                case when p_photoLocation=''
                    then null
                    else p_photoLocation
                end,
                case when p_photoWidth=0
                    then null
                    else p_photoWidth
                end,
                case when p_photoHeight=0
                    then null
                    else p_photoHeight
                end)
            where year=p_year
              and institution=p_institution;
        if found then
            return cast(ret as text) from classes where year = p_year and institution=p_institution;
        end if;
        -- not there so try to insert the class
        -- if someone else inserts the same class
        -- concurrently we should get a unique-key
        -- failure
        begin
            insert into classes(
                year,
                institution,
                photoLocation,
                photoWidth,
                photoHeight)
            values (
                p_year,
                p_institution,
                case when p_photoLocation=''
                    then null
                    else p_photoLocation
                end,
                case when p_photoWidth=0
                    then null
                    else p_photoWidth
                end,
                case when p_photoHeight=0
                    then null
                    else p_photoHeight
                end);
            return cast(ret as text) from currval(pg_get_serial_sequence('classes', 'id')) as ret;
        exception when unique_violation then
            -- do nothing, and loop to try the update again
        end;
    end loop;
end;
$$
language plpgsql;

create or replace function get_class_by_year_and_institution(
        p_year integer,
        p_institution varchar(128))
    returns text as
$$
begin
    return row_to_json(classes)
        from classes
        where year=p_year
          and institution=p_institution;
end;
$$
language plpgsql;

create or replace function get_all_classes()
    returns text as
$$
begin
    return array_to_json(array(select row_to_json(classes) from classes));
end;
$$
language plpgsql;
